<?php
// *** SELECT ID USER *** //
function getUserID($dbb,$userlogin,$userpassword) {
	$sql = "SELECT iduser,userlogin,userpassword 
							FROM `user`
							WHERE userlogin = :userlogin AND userpassword = :userpassword ;";
							
	$user = $dbb->prepare($sql);
	
	$user->bindParam(':userlogin', $userlogin);
	$user->bindParam(':userpassword', $userpassword);
	
	$user->execute();
	
	return $user;
}
// *** ALL CATEGORIES OF USER **//
function getCategories($dbb, $iduser) {
	$categories = $dbb->prepare("SELECT idcategory, categoryname 
							   FROM `category`
							   WHERE id_user = :iduser;");
	$categories->bindParam(':iduser', $iduser);
	$categories->execute();
	return $categories;
}

// *** SELECT ALL TASKS OF USER *** //
function getCurrentTasks($dbb, $iduser) {

	$tasks = $dbb->prepare("SELECT t.idtask, t.taskname, t.taskdescription, t.effectiveenddate, t.taskstart, t.taskend , c.categoryname
							FROM `task` t 
							JOIN `category` c ON c.idcategory = t.id_category 
							JOIN `user` u ON u.iduser = t.id_user 
							WHERE u.iduser = :iduser AND DATE_SUB(taskend,INTERVAL 1 DAY)<=CURDATE();");
							
	$tasks->bindParam(':iduser', $iduser);
	$tasks->execute();
	
	return $tasks;
}

function getFutureTasks($dbb, $iduser) {

	$tasks = $dbb->prepare("SELECT  t.idtask,t.taskname, t.taskdescription, t.effectiveenddate, t.taskstart, t.taskend , c.categoryname
							FROM `task` t 
							JOIN `category` c ON c.idcategory = t.id_category 
							JOIN `user` u ON u.iduser = t.id_user 
							WHERE u.iduser = :iduser AND DATE_SUB(taskend,INTERVAL 1 DAY)>CURDATE();");
							
	$tasks->bindParam(':iduser', $iduser);
	$tasks->execute();
	
	return $tasks;
}

function getTask($dbb, $idtask, $iduser) {

	$tasks = $dbb->prepare("SELECT t.taskname, t.taskdescription, t.taskstart, t.taskend , c.categoryname, t.id_importance
							FROM `task` t 
							JOIN `category` c ON c.idcategory = t.id_category 
							JOIN `user` u ON u.iduser = t.id_user 
							WHERE u.iduser = :iduser AND t.idtask = :idtask;");
							
	$tasks->bindParam(':iduser', $iduser);
	$tasks->bindParam(':idtask', $idtask);
	$tasks->execute();
	
	return $tasks;
}

// *** SELECT USER PROFILE *** //
function getUserProfile($dbb, $iduser) {

	$userprof = $dbb->prepare("select * from user WHERE iduser = :iduser;");
	$userprof->bindParam(':iduser', $iduser);
	$userprof->execute();
	
	return $userprof;
}


// *** SELECT ALL TASKS OF USER *** //
function getTasks($dbb, $idcategory, $iduser) {
	$tasks = $dbb->prepare("SELECT t.taskname, t.taskdescription, t.taskstart, t.taskend, i.importancename, c.idcategory, c.categoryname
							FROM `task` t 
							JOIN `category` c ON c.idcategory = t.id_category 
							JOIN `importance` i ON i.idimportance = t.id_importance 
							JOIN `user` u ON u.iduser = :iduser;");
							
	$tasks->bindParam(':iduser', $iduser);
	$tasks->execute();
	
	return $tasks;
}
// *** SELECT COUNT TASKS IN CATEGORY *** //
function getTasksInCategory($dbb, $idcategory, $iduser) {

	$tasks = $dbb->prepare("SELECT t.idtask, t.taskname, t.taskdescription, t.taskstart, t.taskend, i.importancename 
							FROM `task` t 
							JOIN `category` c ON c.idcategory = t.id_category 
							JOIN `importance` i ON i.idimportance = t.id_importance 
							WHERE c.id_user = :iduser
								AND c.idcategory = :idcategory;");
							
	$tasks->bindParam(':iduser', $iduser);
	$tasks->bindParam(':idcategory', $idcategory);
								
	$tasks->execute();
	
	return $tasks;
}
// *** ADD USER LIGHT MAIL PASSWORD *** //
function addUserLight($dbb, $userlogin, $userpassword) {
	$sql = "INSERT INTO `user` (userlogin, userpassword)
			VALUES (:userlogin,
					:userpassword);";
	
	$users = $dbb->prepare($sql);


	$users->bindParam(':userlogin', $userlogin);
	$users->bindParam(':userpassword', $userpassword);
	
	try {
		$users->execute();
		
			$lastid = $dbb->lastInsertId(); // On Recupere l'ID inséré
			
			generCategory($dbb,$lastid);// On génére des catégories par défaut
			
		return "Félicitation ! Vous êtes inscrit !";
	}
    catch (PDOException $e) {
		return "Erreur lors de l'ajout";
	}
}

// *** ADD USER FULL *** //
function addUser($dbb, $userlastname, $userfirstname,$userlogin, $userpassword ) {
	$sql = "INSERT INTO `user` (userlastname, userfirstname, userlogin, userpassword, userlogin)
	VALUES (:userlastname,:userfirstname,:userlogin,:userpassword,:userlogin);";	
	
	$users = $dbb->prepare($sql);
	
	try {
	$users->execute(array(
						':userlastname' => $userlastname, 
						':userfirstname' => $userfirstname,
						':userlogin' => $userlogin,
						':userpassword' => 	$userpassword,
					));
		
		$lastid = $users->lastInsertId();  // On Recupere l'ID inséré
		
		generCategory($dbb,$lastid); // On génére des catégories par défaut
		
		return "Félicitation ! Vous êtes inscrit !";
	}
    catch (PDOException $e) {
		return "Erreur lors de l'ajout";
	}
}

// *** ADD CATEGORY *** //
function addCategory($dbb, $categoryname, $iduser) {

	$sql = "INSERT INTO `category` (categoryname, id_user) 
			VALUES (:categoryname,:iduser);";

	
	$category = $dbb->prepare($sql);	
	
	$category->bindParam(':categoryname', $categoryname);
	$category->bindParam(':iduser', $iduser);
	try{
		$category->execute();
		return "Catégorie ajoutée !";
	}
    catch (PDOException $e) {
		return "Erreur lors de l'ajout";
	}
}

// *** REMOVE CATEGORY *** //
function removeCategory($dbb, $idcategory) {

	$sql = "DELETE FROM `category` 
			WHERE idcategory = :idcategory;";

	
	$category = $dbb->prepare($sql);	
	
	$category->bindParam(':idcategory', $idcategory);
	try{
		$category->execute();
		return "Catégorie supprimée !";
	}
    catch (PDOException $e) {
		return "Erreur lors de la suppression";
	}
}

// *** DEFAULT GATEGORIES GENERATION*** //
function generCategory($dbb,$iduser) {

	// Préparation requete
	$sql = "INSERT INTO `category` (categoryname, id_user) VALUES (:categoryname,:iduser);";
	$category = $dbb->prepare($sql);

	//Création tableau des catégories par défaut
	$tabcat = array ( 1 => "Personnel",2 => "Travail");
	
	//Ajout des catégories par défaut
	foreach($tabcat as $i => $value){
		try{
			$category->bindParam(':categoryname', $tabcat[$i]);
			$category->bindParam(':iduser', $iduser);
			$category->execute();
		}
		catch (PDOException $e) {
			return "Erreur lors de l'ajout";
		}
	}
}

// *** ADD TASK *** //
function addTask($dbb, $iduser,$taskname, $taskdescription, $idcategory, $taskstart, $taskend, $importance) {

	$sql = "INSERT INTO `task` (taskname, taskdescription, taskstart, taskend,id_category,id_user,id_importance) 
			VALUES (:taskname, :taskdescription, :taskstart, :taskend,:idcategory,:iduser,:idimportance);";
			
	$task = $dbb->prepare($sql);
	
	try {
	$task->execute(array(
						':taskname' => $taskname, 
						':taskdescription' => $taskdescription,
						':taskstart' => $taskstart,
						':taskend' => 	$taskend,
						':idcategory' => 	$idcategory,
						':iduser' => $iduser,
						':idimportance' => $importance
					));
		return "Tâche créée !";
	}
    catch (PDOException $e) {
		return "Erreur dans la création de la tâche ! ";
	}

}

// *** UPDATE USER *** //
function updateUser($dbb,$iduser, $userlastname, $userfirstname,$userbirthday,$useremail, $userpassword, $showdone) {

	if ($userpassword != "") {
			$sql = "UPDATE `user` SET userlastname = :userlastname,userfirstname = :userfirstname,
				useremail = :useremail,userbirthday  = :userbirthday,userpassword = :userpassword, showdone = :showdone WHERE iduser = :iduser;";
			$userup = $dbb->prepare($sql);
			
			try {
				$userup->execute(array(':userlastname' => $userlastname, 
							':userfirstname' => $userfirstname,
							':useremail' => $useremail,
							':userbirthday' => $userbirthday,
							':userpassword' => md5($userpassword),
							':showdone' => 	$showdone,
							':iduser' => $iduser,
						));
						return "Profil mis a jour ! <br /> Mot de passe modifié !";
				}
				catch (PDOException $e) {
					return "Erreur dans la mise a jour ! ";
				}
	}else{
			$sql = "UPDATE `user` SET userlastname = :userlastname,userfirstname = :userfirstname,
				useremail = :useremail,userbirthday  = :userbirthday, showdone = :showdone WHERE iduser = :iduser;";
			$userup = $dbb->prepare($sql);
			
			try {
				$userup->execute(array(':userlastname' => $userlastname, 
							':userfirstname' => $userfirstname,
							':useremail' => $useremail,
							':userbirthday' => $userbirthday,
							':showdone' => 	$showdone,
							':iduser' => $iduser,
						));
						return "Profil mis a jour !<br /> Le mot de passe n'a pas changé !";
				}
				catch (PDOException $e) {
					return "Erreur dans la mise a jour ! ";
				}
	}
}

// *** UPDATE CATEGORY *** //
function updateCategory($dbb, $iduser, $idcategory, $categoryname) {

	$sql = "UPDATE `category` SET categoryname = :categoryname
			WHERE id_category = :idcategory AND id_user = :iduser;";
			
	$categoryup = $dbb->prepare($sql);

	$categoryup->bindParam(':categoryname', $categoryname);
	$categoryup->bindParam(':idcategory', $idcategory);
	$categoryup->bindParam(':iduser', $iduser);
	
	try{
		$categoryup->execute();
		return "Catégorie mise à jour !";
	}
    catch (PDOException $e) {
		return "Erreur lors de la mise à jour";
	}

}
// *** UPDATE TASK *** //
function updateTask($dbb, $idtask, $idcategory, $iduser, $taskname, $taskdescription, $taskstart, $taskend, $importance) {

	$sql = "UPDATE task SET taskname = :taskname, id_importance= :importance, taskdescription = :taskdescription, taskstart = :taskstart,taskend = :taskend, id_category = :idcategory WHERE idtask = :idtask AND id_user = :iduser ;";
	$taskup=$dbb->prepare($sql);
	try {
		$taskup->execute(array(
						':taskname' => $taskname, 
						':taskdescription' => $taskdescription,
						':taskstart' => $taskstart,
						':taskend' => $taskend,
						':idcategory' => $idcategory,
						':idtask' => $idtask,
						':iduser' => $iduser,
						':importance' => $importance
					));
		return "Tâche mise à jour !";
	}
    catch (PDOException $e) {
		return "Erreur dans la mise a jour ! ";
	}
}
function getImportance($dbb) {
	$imp = $dbb->prepare("SELECT idimportance, importancename FROM `importance`;");
	$imp->execute();
	return $imp;
}

function endTask($dbb,$task_id,$user_id)
{
	$sql = "UPDATE task SET effectiveenddate = :enddate WHERE idtask = :idtask AND id_user = :iduser ;";
	$taskup=$dbb->prepare($sql);
	return $taskup->execute(array(
						':enddate' => date('Y-m-d H:i:s'), 
						':idtask' => $task_id,
						':iduser' => $user_id
	));
}
function startTask($dbb,$task_id,$user_id)
{
	$sql = "UPDATE task SET effectiveenddate = :enddate WHERE idtask = :idtask AND id_user = :iduser ;";
	$taskup=$dbb->prepare($sql);
	return $taskup->execute(array(
						':enddate' => NULL, 
						':idtask' => $task_id,
						':iduser' => $user_id
	));
}
?>